package BusinessLogic;

import java.security.InvalidParameterException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import DatabasePackage.CourseDBTable;
import DatabasePackage.CourseDBTable.CourseDBTScheme;
import DatabasePackage.DatabaseManager;
import DatabasePackage.DatabaseManager.DBTables;
import DatabasePackage.ItemAlreadyInDBException;
import DatabasePackage.ItemNotFoundException;
import DatabasePackage.SessionDBTable;
import DatabasePackage.SessionDBTable.SessionDBTScheme;
import DatabasePackage.UserCoursesDBTable;
import DatabasePackage.UserCoursesDBTable.UserCoursesDBTableScheme;
import DatabasePackage.UserDBTable;
import DatabasePackage.UserDBTable.UserDBTScheme;

public class CourseManager extends ActionManager {
  
  public CourseManager(DatabaseManager dbm) {
    super(dbm);
  }
  
  @Action public Result getCourse(Map<String, String[]> params){
    Connection con = null;
    List<String> l = new ArrayList<String>(Arrays.asList(CourseDBTScheme.NAME.toString()));
    Course c = null;
    try{
      try{
        validateParams(params, l);
      } catch(InvalidParameterException e){
        return createFailureResult("One or more parameters are missing");
      }
      String cName = getStringParam(params,CourseDBTScheme.NAME.toString());
      CourseDBTable table = (CourseDBTable) dbMng.getTable(DBTables.COURSE);
      try {
        con = table.connect();
      } catch (Exception e) {
        return createFailureResult("Cannot connect to the DB");
      }
      con.setAutoCommit(false);
      c = table.getCourse(con, cName);
      if(c == null)
        return createFailureResult("getCourse: There is no such course: "+cName);
      try {
        con.commit();
      } catch (SQLException e) {
        con.rollback();
        return createNewCourse(params);
      }
    }catch(Exception e){
      createFailureResult("The DB encountered a problem "+e.getMessage());
    }finally{
      safeCloseConnection(con);
    }
    return createSuccessResult(c);       
  }
      
  @Action public Result getSessionsByCourse(Map<String, String[]> params){
    Connection con = null;
    List<String> l = new ArrayList<String>(Arrays.asList(CourseDBTScheme.NAME.toString()));
    List<Session> sessions = null;
    try{
      try{
        validateParams(params, l);
      } catch(InvalidParameterException e){
        return createFailureResult("One or more parameters are missing");
      }
      String cName = getStringParam(params,CourseDBTScheme.NAME.toString());
      SessionDBTable table = (SessionDBTable) dbMng.getTable(DBTables.SESSION);
      try {
        con = table.connect();
      } catch (Exception e) {
        return createFailureResult("Cannot connect to the DB");
      }
      con.setAutoCommit(false);
      sessions = table.getSessions(con, cName);
      try {
        con.commit();
      } catch (SQLException e) {
        con.rollback();
        return createNewCourse(params);
      }
    }catch(Exception e){
      createFailureResult("The DB encountered a problem "+e.getMessage());
    }finally{
      safeCloseConnection(con);
    }
    return createSuccessResult(sessions);       
  }
  
  
  @Action public Result createNewCourse(Map<String, String[]> params){
    Connection con = null;
    List<String> l = new ArrayList<String>(Arrays.asList(CourseDBTScheme.NAME.toString(),CourseDBTScheme.DESCRIPTION.toString()
        ,CourseDBTScheme.POINTS.toString(),CourseDBTScheme.CAPACITY.toString(),CourseDBTScheme.GROUP_NUM.toString(),CourseDBTScheme.ADMIN.toString(),
         SessionDBTScheme.DAY.toString(), SessionDBTScheme.START_TIME.toString(),SessionDBTScheme.END_TIME.toString()));
    try{ 
      try{
        validateParams(params, l);
      } catch(InvalidParameterException e){
        return createFailureResult("One or more parameters are missing");
      }
      String day = getStringParam(params,SessionDBTScheme.DAY.toString());
	  int from = getIntParam(params,SessionDBTScheme.START_TIME.toString());
	  int to = getIntParam(params,SessionDBTScheme.END_TIME.toString());
	  //legal values for day, from, to
	  Result res = validateAddSessionParams(day,from,to);
	  if (res!= null)
		  return res;
	  SessionDBTable session = (SessionDBTable) dbMng.getTable(DBTables.SESSION);
	  Session toAdd = new Session(getStringParam(params,CourseDBTScheme.NAME.toString()), day, from, to);
      CourseDBTable table = (CourseDBTable) dbMng.getTable(DBTables.COURSE);
      UserDBTable userTable = (UserDBTable) dbMng.getTable(DBTables.USER);
      try {
        con = table.connect();
      } catch (Exception e) {
        return createFailureResult("Cannot connect to the DB");
      }
      try {
        con.setAutoCommit(false);
      } catch (SQLException e) {
        return createFailureResult("The DB encountered a problem");
      }
      try{
        checkValuesInsert(params);
      }catch ( InvalidParameterException e){
        return createFailureResult(e.getMessage());
      }
      User admin=null;
      admin = userTable.getUser(con, getStringParam(params, CourseDBTScheme.ADMIN.toString()));
      if(admin == null)
        return createFailureResult("the admin who try to create the course doesnt exist in the DB");
      if (!admin.getRole().equals("admin")){
        return createFailureResult("the user who try to create the course is not an admin");
      }
      Course  c =  null;
      try{
        c = createCourse(params); 
      }catch (NumberFormatException e){
        String err = e.getMessage();
        String err2 = err.replaceAll("\"", "'");
        return createFailureResult(err2);
      }
      Course exist = table.getCourse(con, CourseDBTScheme.NAME.toString());
      if (exist != null)
        return createFailureResult("Course with the same name already exists");
      List<Course> others = table.getCoursesOfGroup(con,c.getGroup());
	  List<Session> sessions = new LinkedList<Session>();
	  for (Course other : others)
		  sessions.addAll(session.getSessions(con, other.getName()));
	  if (overlap(sessions,toAdd))
		  return createFailureResult("The requested session overlaps other course sessions");
      try {
    	session.insertSession(con, toAdd);
        table.insertCourse(con, c);
      } catch (ItemAlreadyInDBException e) {
        return createFailureResult("Course with the same name already exists");
      } catch (Exception e) {
        return createFailureResult("The DB encountered a problem "+e.getMessage());
      }
      try {
        con.commit();
      } catch (SQLException e) {
        con.rollback();
        return createNewCourse(params);
      }
    }catch(Exception e){
      createFailureResult("The DB encountered a problem "+e.getMessage());
    }finally{
      safeCloseConnection(con);
    }
    return createSuccessResult(null); 
  }

  private Course createCourse(Map<String, String[]> params) throws NumberFormatException {
    String name = getStringParam(params,CourseDBTScheme.NAME.toString());
    String desc = getStringParam(params,CourseDBTScheme.DESCRIPTION.toString());
    float points = getFloatParam(params,CourseDBTScheme.POINTS.toString());
    int capacity = getIntParam(params,CourseDBTScheme.CAPACITY.toString());
    int group = getIntParam(params,CourseDBTScheme.GROUP_NUM.toString());
    String admin = getStringParam(params,CourseDBTScheme.ADMIN.toString());
    return new Course(name, desc, points, capacity, group, admin);
  }

  private void checkValuesInsert(Map<String, String[]> params) {
    String name = getStringParam(params,CourseDBTScheme.NAME.toString());
    String desc = getStringParam(params,CourseDBTScheme.DESCRIPTION.toString());
    String points = getStringParam(params,CourseDBTScheme.POINTS.toString());
    String capacity = getStringParam(params,CourseDBTScheme.CAPACITY.toString());
    String group = getStringParam(params,CourseDBTScheme.GROUP_NUM.toString());
    String admin = getStringParam(params,CourseDBTScheme.ADMIN.toString());
    if (name.equals("")||desc.equals("")||points.equals("")||capacity.equals("")||group.equals("")||admin.equals("")){
      throw new InvalidParameterException("One or more empty parameters");
    }
    if(name.length()>255) throw new InvalidParameterException("The name must be at most 255 characters");
    if(desc.length()>2000) throw new InvalidParameterException("The name must be at most 2000 characters");
    if(points.length()>10) throw new InvalidParameterException("The course points string is to long (10 char at most)");
    if(capacity.length()>10) throw new InvalidParameterException("The capacity string is to long (10 char at most)");
    if(group.length()>10) throw new InvalidParameterException("The group string is to long (10 char at most)");
    if(admin.length()>255) throw new InvalidParameterException("The admin's name must be at most 255 characters");
  }
  
  
  @Action public Result deleteCourse(Map<String, String[]> params){
    Connection con = null;
    List<String> l = new ArrayList<String>(Arrays.asList(CourseDBTScheme.NAME.toString(),CourseDBTScheme.ADMIN.toString()));
    try{
      try{ 
        validateParams(params, l);
      } catch(InvalidParameterException e){
        return createFailureResult("One or more parameters are missing");
      }
      CourseDBTable table = (CourseDBTable) dbMng.getTable(DBTables.COURSE);
      try {
        con = table.connect();
      } catch (Exception e) {
        return createFailureResult("Cannot connect to the DB");
      }
      try {
        con.setAutoCommit(false);
      } catch (SQLException e) {
        return createFailureResult("The DB encountered a problem");
      }
      try{
        checkValuesDelete(params);
      }catch ( InvalidParameterException e){
        return createFailureResult(e.getMessage());
      }
      String name = getStringParam(params,CourseDBTScheme.NAME.toString());
      String admin = getStringParam(params,CourseDBTScheme.ADMIN.toString());
      SessionDBTable sessionTable = (SessionDBTable) dbMng.getTable(DBTables.SESSION);
      UserCoursesDBTable userCourse = (UserCoursesDBTable) dbMng.getTable(DBTables.USER_COURSE);
      try{
        sessionTable.deleteAllSessions(con, name);
        userCourse.deleteCourseStudents(con, name);
      }catch(ItemNotFoundException e){}
      Course c= null;
      try {
        c = table.getCourse(con, name);
      } catch (Exception e) {
        return createFailureResult("The DB encountered a problem");
      }
      if(c == null) return createFailureResult("There is no such course, so you cant delete it");
      String courseAdmin = c.getAdmin();
      if(!courseAdmin.equals(admin))
        return createFailureResult("only the admin who created the course can delete it");
      try {
        table.deleteCourse(con, name);
      } catch (ItemNotFoundException e) {
        return createFailureResult("There is no such user. You cant delete him");
      } catch (Exception e) {
        return createFailureResult("The DB encountered a problem");
      }

      try {
        con.commit();
      } catch (SQLException e) {
          con.rollback();
          return deleteCourse(params);
      }
    }catch(Exception e){
      createFailureResult("The DB encountered a problem");
    }finally{
      safeCloseConnection(con);
    }
    return createSuccessResult(null); 
   }

  private void checkValuesDelete(Map<String, String[]> params) {
    String name = getStringParam(params,CourseDBTScheme.NAME.toString());
    String admin = getStringParam(params,CourseDBTScheme.ADMIN.toString());
    if (name.equals("")||admin.equals("")){
      throw new InvalidParameterException("One or more empty parameters");
    }
    if(name.length()>255) throw new InvalidParameterException("The name must be at most 255 characters");
    if(admin.length()>255) throw new InvalidParameterException("The admin's name must be at most 255 characters");
  }
   
  @Action public Result addCourseSession(Map<String, String[]> params)
  {
	 Connection con = null;
	 try
	 {	  
	  List<String> l = new ArrayList<String>(Arrays.asList(SessionDBTScheme.COURSE_NAME.toString(),SessionDBTScheme.DAY.toString(),
			  SessionDBTScheme.START_TIME.toString(),CourseDBTScheme.ADMIN.toString(),SessionDBTScheme.END_TIME.toString()));
    try{
      validateParams(params, l);
    } catch(InvalidParameterException e){
      return createFailureResult("One or more parameters are missing");
    }
	  String course = getStringParam(params,SessionDBTScheme.COURSE_NAME.toString());
	  String day = getStringParam(params,SessionDBTScheme.DAY.toString());
	  int from = getIntParam(params,SessionDBTScheme.START_TIME.toString());
	  int to = getIntParam(params,SessionDBTScheme.END_TIME.toString());
	  String admin = getStringParam(params,CourseDBTScheme.ADMIN.toString());
	  //legal values for day, from, to
	  Result res = validateAddSessionParams(day,from,to);
	  if (res!= null)
		  return res;
	  SessionDBTable session = (SessionDBTable) dbMng.getTable(DBTables.SESSION);
	  UserCoursesDBTable userCourseDB = (UserCoursesDBTable) dbMng.getTable(DBTables.USER_COURSE);
	  Session toAdd = new Session(course, day, from, to);
	  con = session.connect();
	  con.setAutoCommit(false);
	  CourseDBTable courseDB = (CourseDBTable) dbMng.getTable(DBTables.COURSE);
	  //check that the course exists
	  Course c = courseDB.getCourse(con, course);
	  if (c== null) 
		  return createFailureResult("The requested course doesn't exist");
	  //don't allow to add session if the course already has registered students!! 
	  //you didn't think of that, didn't you...
	  List<String> users = userCourseDB.getUsersByCourse(con, course);
	  if (!users.isEmpty())
		  return createFailureResult("You cannot add a session to a course that already has students");
	  String courseAdmin = c.getAdmin();
      if(!courseAdmin.equals(admin))
        return createFailureResult("only the admin who created the course can add session");
	  //check not overlapping session of courses from the same group
	  List<Course> others = courseDB.getCoursesOfGroup(con,c.getGroup());
	  List<Session> sessions = session.getSessions(con, course);
	  for (Course other : others)
		  sessions.addAll(session.getSessions(con, other.getName()));
	  if (overlap(sessions,toAdd))
		  return createFailureResult("The requested session overlaps other course sessions");
	  session.insertSession(con, toAdd);
	  try{
		  con.commit();
	  }
	  catch (SQLException e)
	  {
		  con.rollback();
		  addCourseSession(params);
	  }
	  return createSuccessResult(null);
	 }catch (Exception e)
	 {
		 return createFailureResult(e.getMessage());
	 }
	 finally{
		 safeCloseConnection(con);
	 }
  }
  
  
  @Action public Result deleteCourseSession(Map<String, String[]> params)
  {
	 Connection con = null;
	 try
	 {	  
	  List<String> l = new ArrayList<String>(Arrays.asList(SessionDBTScheme.COURSE_NAME.toString(),SessionDBTScheme.DAY.toString(),
			  SessionDBTScheme.START_TIME.toString(),CourseDBTScheme.ADMIN.toString(),SessionDBTScheme.END_TIME.toString()));
    try{
      validateParams(params, l);
    } catch(InvalidParameterException e){
      return createFailureResult("One or more parameters are missing");
    }
	  String course = getStringParam(params,SessionDBTScheme.COURSE_NAME.toString());
	  String day = getStringParam(params,SessionDBTScheme.DAY.toString());
	  int from = getIntParam(params,SessionDBTScheme.START_TIME.toString());
	  int to = getIntParam(params,SessionDBTScheme.END_TIME.toString());
	  String admin = getStringParam(params,CourseDBTScheme.ADMIN.toString());
	  //legal values for day, from, to
	  Result res = validateAddSessionParams(day,from,to);
	  if (res!= null)
		  return res;
	  SessionDBTable session = (SessionDBTable) dbMng.getTable(DBTables.SESSION);
	  CourseDBTable courseDB = (CourseDBTable) dbMng.getTable(DBTables.COURSE);
	  Session toDelete = new Session(course, day, from, to);
	  con = session.connect();
	  con.setAutoCommit(false);
	  //check that the course exists
	  Course c = courseDB.getCourse(con, course);
	  if (c== null) 
		  return createFailureResult("The requested course doesn't exist");
	  String courseAdmin = c.getAdmin();
      if(!courseAdmin.equals(admin))
        return createFailureResult("only the admin who created the course can delete session");
	  //check that the course has at least two sessions
	  List<Session> sessions = session.getSessions(con, course);
	  if (sessions.size()<2)
		  return createFailureResult("Deleting a course's only session is not allowed");
	  try{session.deleteSession(con, toDelete);}
	  catch(ItemNotFoundException i){return createFailureResult("The requested session doesn't exist");}
	  try{
		  con.commit();
	  }
	  catch (SQLException e)
	  {
		  con.rollback();
		  deleteCourseSession(params);
	  }
	  return createSuccessResult(null);
	 }catch (Exception e)
	 {
		 return createFailureResult(e.getMessage());
	 }
	 finally{
		 safeCloseConnection(con);
	 }
  }


private Result validateAddSessionParams(String day, int from,int to) {
	
	if (!day.equals("Sunday")&&!day.equals("Monday")&&!day.equals("Tuesday")&&
			!day.equals("Wednesday")&&!day.equals("Thursday")&&!day.equals("Friday")&&
			!day.equals("Saturday"))
		return createFailureResult("Incorrect day");
	if (from<8 || from > 18 || to <8 || to > 18 )
		return createFailureResult("Invalid session hours");
	if (to-from>4 || to-from<1)
		return createFailureResult("A session must last between 1 to 4 hours");
	return null;
}

	private boolean overlap(List<Session> sessions, Session toAdd) {
		for(Session check : sessions)
		{
			if (check.getDay().equals(toAdd.getDay()))
			{
				if (!((check.getStart_time()<toAdd.getStart_time() && check.getEnd_time()<=toAdd.getStart_time())
						||(toAdd.getStart_time()<check.getStart_time() && toAdd.getEnd_time()<=check.getStart_time())))
					return true;
			}
		}
		return false;
	}
	
	@Action public Result getAllCourses(Map<String, String[]> params)
	{
		Connection con = null;
		try
		{
			CourseDBTable courseDB = (CourseDBTable) dbMng.getTable(DBTables.COURSE);
			SessionDBTable sessionDB = (SessionDBTable) dbMng.getTable(DBTables.SESSION);
			con = courseDB.connect();
			List<Session> sessions = new LinkedList<Session>();
			con.setAutoCommit(false);
			//get all courses
			List<Course> courses = courseDB.getAllCourses(con);
			//get all sessions
			for (Course course : courses)
				sessions.addAll(sessionDB.getSessions(con, course.getName()));
			try
			{
				con.commit();
			}
			catch(SQLException e)
			{
				con.rollback();
				return getAllCourses(params);
			}
			return createSuccessResult(new CoursesContainer(courses, sessions));
		}
		catch(Exception e)
		{
			return createFailureResult(e.getMessage());
		}
		finally
		{
			safeCloseConnection(con);
		}
	}
	
	@Action public Result getTimetable(Map<String, String[]> params)
	{
		List<String> l = new ArrayList<String>(Arrays.asList(UserCoursesDBTableScheme.USER.toString()));
		validateParams(params, l);
		Connection con = null;
		try
		{
			UserCoursesDBTable ucourseDB = (UserCoursesDBTable) dbMng.getTable(DBTables.USER_COURSE);
			CourseDBTable courseDB = (CourseDBTable) dbMng.getTable(DBTables.COURSE);
			SessionDBTable sessionDB = (SessionDBTable) dbMng.getTable(DBTables.SESSION);
			con = courseDB.connect();
			List<Session> sessions = new LinkedList<Session>();
			List<Course> courses = new LinkedList<Course>();
			con.setAutoCommit(false);
			//get all courses and sessions
			List<String> courseNames = ucourseDB.getUserCourses(con, getStringParam(params, UserCoursesDBTableScheme.USER.toString()));
			for (String courseName : courseNames)
			{
				courses.add(courseDB.getCourse(con, courseName));
				sessions.addAll(sessionDB.getSessions(con, courseName));
			}
			try
			{
				con.commit();
			}
			catch(SQLException e)
			{
				con.rollback();
				return getAllCourses(params);
			}
			return createSuccessResult(new CoursesContainer(courses, sessions));
		}
		catch(Exception e)
		{
			return createFailureResult(e.getMessage());
		}
		finally
		{
			safeCloseConnection(con);
		}
	}
	
	@Action public Result register(Map<String, String[]> params){
	   Connection con = null;
	   List<String> l = new ArrayList<String>(Arrays.asList(UserDBTScheme.USER_NAME.toString(),UserCoursesDBTableScheme.COURSE.toString()));
	   try{
      try{
        validateParams(params, l);
      } catch(InvalidParameterException e){
        return createFailureResult("One or more parameters are missing");
      }
      String userName = getStringParam(params,UserDBTScheme.USER_NAME.toString());
      String courseName = getStringParam(params,UserCoursesDBTableScheme.COURSE.toString());
      CourseDBTable courseTable = (CourseDBTable) dbMng.getTable(DBTables.COURSE);
      UserCoursesDBTable userCoursesTable = (UserCoursesDBTable) dbMng.getTable(DBTables.USER_COURSE);
      UserDBTable userTable = (UserDBTable) dbMng.getTable(DBTables.USER);
      try {
        con = courseTable.connect();
      } catch (Exception e) {
        return createFailureResult("Cannot connect to the DB");
      }
      con.setAutoCommit(false);
      Course course = null;
      course = courseTable.getCourse(con, courseName);
      if (course == null)
        return createFailureResult("There is no such course");
      User u = userTable.getUser(con, userName);
      if (u==null)
        return createFailureResult("There is no such user");
      
      if(userCoursesTable.tupleExist(con,userName,courseName)){
        return createFailureResult("The user is already registered to this course.");
      }
      int capacity = course.getCapacity();
      int numRegistered = getNumRegistered(con, course.getName());
      if(numRegistered==capacity){
        return createFailureResult("full capacity");
      }
      if (registeredToOverlapsCourses(con,userName,courseName)){
        return createFailureResult("The user is already registered to another course which overlaps this course.");
      }
      if (tooManyGroups(con,userName,courseName))
        return createFailureResult("The user is already registered to 3 other groups.");
      try {
        userCoursesTable.insertUserCourseTuple(con, userName, courseName);
      } catch (ItemAlreadyInDBException e) {
        return createFailureResult("The user is already registered to this course.");
      } 
      try {
        con.commit();
      } catch (SQLException e) {
        con.rollback();
        return register(params);
      }
    }catch(Exception e){
      createFailureResult("The DB encountered a problem "+e.getMessage());
    }finally{
      safeCloseConnection(con);
    }

    String userName = getStringParam(params,UserDBTScheme.USER_NAME.toString());
    String courseName = getStringParam(params,UserCoursesDBTableScheme.COURSE.toString());

    return createSuccessResult(null);
	}
	
  @Action public Result unregister(Map<String, String[]> params){
    Connection con = null;
    List<String> l = new ArrayList<String>(Arrays.asList(UserDBTScheme.USER_NAME.toString(),UserCoursesDBTableScheme.COURSE.toString()));
    try{
      try {
        validateParams(params, l);
      }catch(InvalidParameterException e){
        return createFailureResult("One or more parameters are missing");
      }
      String userName = getStringParam(params,UserDBTScheme.USER_NAME.toString());
      String courseName = getStringParam(params,UserCoursesDBTableScheme.COURSE.toString());
      CourseDBTable courseTable = (CourseDBTable) dbMng.getTable(DBTables.COURSE);
      UserCoursesDBTable userCoursesTable = (UserCoursesDBTable) dbMng.getTable(DBTables.USER_COURSE);
      UserDBTable userTable = (UserDBTable) dbMng.getTable(DBTables.USER);
      try {
        con = courseTable.connect();
      } catch (Exception e) {
        return createFailureResult("Cannot connect to the DB");
      }
      con.setAutoCommit(false);
      User user = userTable.getUser(con,userName);
      if (user == null)
        return createFailureResult("There is no such user");
      Course course = courseTable.getCourse(con, courseName);
      if (course == null)
        return createFailureResult("There is no such course");
      try {
        userCoursesTable.deleteUserCourseTuple(con, courseName, userName);
      } catch (ItemNotFoundException e) {
        return createFailureResult("The user: "+userName+" is not registered for the course: "+ courseName);
      } 
      try {
        con.commit();
      } catch (SQLException e) {
        con.rollback();
        return unregister(params);
      }
    }catch(Exception e){
      createFailureResult("The DB encountered a problem "+e.getMessage());
    }finally{
      safeCloseConnection(con);
    }
    return createSuccessResult(null);
  }
	
	
	 @Action public Result availableCourses(Map<String, String[]> params){
     Connection con = null;
     List<String> l = new ArrayList<String>(Arrays.asList(UserDBTScheme.USER_NAME.toString()));
     List<Course> availableCoursesList = new LinkedList<Course>();
     try{
        try{
          validateParams(params, l);
        } catch(InvalidParameterException e){
          return createFailureResult("One or more parameters are missing");
        }
        CourseDBTable courseTable = (CourseDBTable) dbMng.getTable(DBTables.COURSE);
        UserDBTable userTable = (UserDBTable) dbMng.getTable(DBTables.USER);
        UserCoursesDBTable userCoursesTable = (UserCoursesDBTable) dbMng.getTable(DBTables.USER_COURSE);
        try {
          con = courseTable.connect();
        } catch (Exception e) {
          return createFailureResult("Cannot connect to the DB");
        }
        con.setAutoCommit(false);
        String userName = getStringParam(params,UserDBTScheme.USER_NAME.toString());
        User u = userTable.getUser(con, userName);
        if (u==null)
          return createFailureResult("There is no such user");
        List<Course> allCourses = courseTable.getAllCourses(con);
        for(Course c : allCourses){
          String courseName = c.getName();
          if (userCoursesTable.tupleExist(con, userName, courseName)){
            continue;
          }
          if (tooManyGroups(con,userName,courseName)){
            continue;
          }
          if (registeredToOverlapsCourses(con,userName,courseName)){
            continue;
          }

          int capacity = c.getCapacity();
          int numRegistered = getNumRegistered(con, courseName);
          if(numRegistered==capacity){
            continue;
          }
          availableCoursesList.add(c);
        }
        try {
          con.commit();
        } catch (SQLException e) {
          con.rollback();
          return availableCourses(params);
        }
     }catch(Exception e){
       createFailureResult("The DB encountered a problem "+e.getMessage());
     }finally{
       safeCloseConnection(con);
     } 
     return createSuccessResult(availableCoursesList);
	 }
	
	
	private boolean tooManyGroups(Connection con, String userName, String courseName) throws SQLException, ClassNotFoundException {
    UserCoursesDBTable userCoursesTable = (UserCoursesDBTable) dbMng.getTable(DBTables.USER_COURSE);
    CourseDBTable courseDB = (CourseDBTable) dbMng.getTable(DBTables.COURSE);
    List<String> coursesNames = userCoursesTable.getUserCourses(con, userName);
    List<Course> courses = new LinkedList<Course>();
    for (String name : coursesNames)
      courses.add(courseDB.getCourse(con, name));
    HashSet<Integer> groups = new HashSet<Integer>();
    for (Course c : courses)
      groups.add(c.getGroup());
    Course newCourse = courseDB.getCourse(con, courseName);
    groups.add(newCourse.getGroup());
    if(groups.size()>3)return true;
    return false;
  }

  private boolean registeredToOverlapsCourses(Connection con, String userName, String courseName) throws SQLException, ClassNotFoundException {
	  UserCoursesDBTable userCoursesTable = (UserCoursesDBTable) dbMng.getTable(DBTables.USER_COURSE);
	  SessionDBTable sessionsTable = (SessionDBTable) dbMng.getTable(DBTables.SESSION);
    List<String> courses = userCoursesTable.getUserCourses(con, userName);
    if(courses.isEmpty()) return false;
    List<Session> sessions = new LinkedList<Session>();
    for (String c : courses){
      sessions.addAll(sessionsTable.getSessions(con, c));
    }
    List<Session> newCourseSessions = sessionsTable.getSessions(con, courseName);
    for (Session s : newCourseSessions){
      if(overlap(sessions, s)) return true;
    }
    return false;
  }

  private int getNumRegistered(Connection con, String course) throws ClassNotFoundException, SQLException{
	  UserCoursesDBTable table = (UserCoursesDBTable) dbMng.getTable(DBTables.USER_COURSE);
	  List<String> users = table.getUsersByCourse(con, course);
	  return users.size();
	}
	
  @Action public Result searchCourses(Map<String, String[]> params)
	{
		Connection con = null;
		List<String> l = new ArrayList<String>(Arrays.asList(CourseDBTScheme.DESCRIPTION.toString(),CourseDBTScheme.GROUP_NUM.toString(),
				CourseDBTScheme.POINTS.toString(),"Sunday","Monday","Tuesday",
				"Wednesday","Thursday","Friday","Saturday"));
	    try{
	       try{
	         validateParams(params, l);
	       	} catch(InvalidParameterException e){
	       		return createFailureResult("One or more parameters are missing");
	        }
			CourseDBTable courseDB = (CourseDBTable) dbMng.getTable(DBTables.COURSE);
			SessionDBTable sessionDB = (SessionDBTable) dbMng.getTable(DBTables.SESSION);
			con = courseDB.connect();
			List<Session> sessions = new LinkedList<Session>();
			con.setAutoCommit(false);
			//get all courses
			List<Course> courses = courseDB.getAllCoursesBy(con,getStringParam(params, CourseDBTScheme.DESCRIPTION.toString()));
			//get all sessions
			for (Course course : courses)
				sessions.addAll(sessionDB.getSessions(con, course.getName()));
			try
			{
				con.commit();
			}
			catch(SQLException e)
			{
				con.rollback();
				return getAllCourses(params);
			}
			return createSuccessResult(
					rearrange(
						new CoursesContainer(courses, sessions),getStringParam(params, CourseDBTScheme.GROUP_NUM.toString()),
						getStringParam(params, CourseDBTScheme.POINTS.toString()),
						getStringParam(params, "Sunday"),getStringParam(params, "Monday"),
						getStringParam(params, "Tuesday"),getStringParam(params, "Wednesday"),
						getStringParam(params, "Thursday"),getStringParam(params, "Friday"),
						getStringParam(params, "Saturday")
						)
					);
		}
		catch(Exception e)
		{
			return createFailureResult(e.getMessage());
		}
		finally
		{
			safeCloseConnection(con);
		}
	}

private CoursesContainer rearrange(CoursesContainer courses, String group,
		String points, String sunday, String monday,
		String tuesday, String wednesday, String thursday,
		String friday, String saturday) {
	Map<Course,Integer> rank = new HashMap<Course, Integer>();
	for (Course c : courses.getCourses())
	{
		int grade = 0;
		try{
		if (c.getGroup() == Integer.parseInt(group))
			grade++;
		}catch (NumberFormatException e){}
		try{
			if (c.getPoints() == Float.parseFloat(points))
				grade++;
		}catch (NumberFormatException e){}
		List<Session> sessions = courses.getSessionsOfCourse(c.getName());
		for (Session session : sessions)
		{
			if (session.getDay().equals("Sunday")&&sunday.toUpperCase().equals("TRUE"))
				grade++;
			if (session.getDay().equals("Monday")&&monday.toUpperCase().equals("TRUE"))
				grade++;
			if (session.getDay().equals("Tuesday")&&tuesday.toUpperCase().equals("TRUE"))
				grade++;
			if (session.getDay().equals("Wednesday")&&wednesday.toUpperCase().equals("TRUE"))
				grade++;
			if (session.getDay().equals("Thursday")&&thursday.toUpperCase().equals("TRUE"))
				grade++;
			if (session.getDay().equals("Friday")&&friday.toUpperCase().equals("TRUE"))
				grade++;
			if (session.getDay().equals("Saturday")&&saturday.toUpperCase().equals("TRUE"))
				grade++;
		}
		rank.put(c, grade);
	}
	List<Course> courseList= sortByValue(rank);
	return new CoursesContainer(courseList,courses.getSessions());
}
	private static List<Course> sortByValue(Map<Course,Integer> map) {
	    List<Map.Entry<Course,Integer>> list = new LinkedList<Map.Entry<Course,Integer>>(map.entrySet());
	    Collections.sort(list, new Comparator<Entry<Course, Integer>>() {
			@Override
			public int compare(Entry<Course, Integer> arg0,
					Entry<Course, Integer> arg1) {
				return arg0.getValue().compareTo(arg1.getValue());
			}
	    });
	   Collections.reverse(list);
	   List<Course> $ = new LinkedList<Course>();
	   for (Map.Entry<Course,Integer> ent : list)
		   $.add(ent.getKey());
	   return $;
	} 
}
